﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;

namespace WebSite.Admin.Core
{
    /// <summary>
    ///dbHelper 的摘要说明
    /// </summary>
    public class dbHelper
    {
        public dbHelper()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        //执行存储过程后返回的对象
        public class pageproret
        {
            public int pagecount { get; set; } //分页总数
            public int totalcount { get; set; } //总记录数
            public DataTable recordset { get; set; } //记录集 
        }
        /// <summary>
        /// 防SQL注入
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        public static string FilterSql( string source )
        {
            if (string.IsNullOrEmpty( source ))
                return null;
            source = source.Replace( "'", "''" );
            //source = source.Replace(" ", "&nbsp;");
            source = source.Replace( "\"", "&#34;" );
            source = source.Replace( ";", "&#59;" );
            source = source.Replace( "<", "&#34;" );
            source = source.Replace( ">", "&#62;" );
            source = source.Replace( "%", "&#37;" );
            source = source.Replace( "\\", "&#92;" );
            source = source.Replace( "--", "&#45;&#45;" );
            return source;
        }
        #region 获得数据库连接

        private string _connectionStrings = string.Empty;
        public string ConnectionStrings
        {
            get
            {
                if (string.IsNullOrEmpty( _connectionStrings ))
                {
                    _connectionStrings = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
                }
                return _connectionStrings;
            }
        }

        public SqlConnection GetConnection()
        {
            string connectionStrings = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();

            SqlConnection myConn = new SqlConnection( connectionStrings );
            return myConn;
        }
        public void closeConn()
        {
            SqlConnection mycon = GetConnection();
            if (Convert.ToBoolean( mycon.State == ConnectionState.Open ))
            {
                mycon.Close();
            }
        }

        #endregion


        //public static void CheckIsLogin()
        //{
        //    if (HttpContext.Current.Session["AdminUser"] == null || HttpContext.Current.Session["AdminUser"] == string.Empty)
        //    {
        //        HttpContext.Current.Response.Redirect( "/Default.aspx" );
        //    }
        //}

        #region 获得cmd
        /// <summary>
        /// 执行存储过程，返回sqlCommand类对象
        /// </summary>
        /// <param name="strProcName"></param>
        /// <returns></returns>
        public SqlCommand GetCommandProc( string strProcName )
        {
            SqlConnection myConn = GetConnection();
            SqlCommand myCmd = new SqlCommand();
            myCmd.Connection = myConn;
            myCmd.CommandText = strProcName;
            myCmd.CommandType = CommandType.StoredProcedure;
            return myCmd;
        }
        /// <summary>
        /// 执行查询语句，返回sqlCommand类对象
        /// </summary>
        /// <param name="strSql">查询语句</param>
        /// <returns>返回sqlCommand类对象</returns>
        public SqlCommand GetCommandStr( string strSql )
        {
            SqlConnection myConn = GetConnection();
            SqlCommand myCmd = new SqlCommand();
            myCmd.Connection = myConn;
            myCmd.CommandText = strSql;
            myCmd.CommandType = CommandType.Text;
            return myCmd;
        }
        #endregion

        #region 获得DataReader
        /// <summary> 
        /// 获得一个DataReader,格式:GetDataReader(Sql命令) 
        /// </summary>
        /// <param name="SqlCommand"></param> 
        /// <param name="conn"></param> 
        /// <returns></returns> 
        public SqlDataReader GetDataReader( string procname, string sqlstr, SqlParameter[] cmdParms )
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                if (procname != null)
                {
                    cmd = GetCommandProc( procname );
                }
                else
                {
                    cmd = GetCommandStr( sqlstr );
                }
                runcmd( cmd, null, cmdParms );
                SqlDataReader rdr = cmd.ExecuteReader( CommandBehavior.CloseConnection );
                cmd.Parameters.Clear();
                return rdr;
            }
            catch (Exception ex)
            {
                throw new Exception( ex.Message, ex );

            }
            finally
            {
                //if (cmd.Connection.State == ConnectionState.Open)
                //{
                //    cmd.Connection.Close();
                //cmd.Connection.Dispose();

                // }
            }


        }
        #endregion

        #region 返回dataset对象


        /// <summary>
        /// 
        /// </summary>
        /// <param name="procname"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public DataSet returndataset( string procname, string sqlstr, SqlParameter[] cmdParms, string tbname )
        {



            SqlDataAdapter adapt = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();


            try
            {

                if (procname != null)
                {
                    cmd = GetCommandProc( procname );
                }
                else
                {
                    cmd = GetCommandStr( sqlstr );
                }
                runcmd( cmd, null, cmdParms );
                adapt = new SqlDataAdapter( cmd );
                if (tbname != null)
                {
                    adapt.Fill( ds, "tbname" );
                }
                else
                {
                    adapt.Fill( ds );
                }
                cmd.Parameters.Clear();
                return ds;

            }
            catch (Exception ex)
            {
                throw new Exception( ex.Message, ex );

            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                {
                    cmd.Connection.Close();

                }
            }
        }

        #endregion

        #region 获得ExecuteScalar
        /// <summary> 
        /// 获得一个ExecuteScalar,格式:ExecuteScalar(Sql命令) 
        /// </summary> 
        /// <param name="SqlCommand"></param> 
        /// <param name="conn"></param> 
        /// <returns></returns>
        public string GetExecuteScalar( string procname, string sqlstr, SqlParameter[] cmdParms )
        {
            SqlCommand cmd = new SqlCommand();
            try
            {

                if (procname != null)
                {
                    cmd = GetCommandProc( procname );
                }
                else
                {
                    cmd = GetCommandStr( sqlstr );
                }
                runcmd( cmd, null, cmdParms );
                string val = cmd.ExecuteScalar().ToString();
                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                throw new Exception( ex.Message, ex );

            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                {
                    cmd.Connection.Close();

                }
            }



        }

        #endregion

        #region 执行sql并返回受影响行数,或存储过程的返回值 增删改

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdTxt"></param>
        public int GetExecuteNonQuery( string procname, string sqlstr, SqlParameter[] cmdParms )
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                if (procname != null)
                {
                    cmd = GetCommandProc( procname );
                }
                else
                {
                    cmd = GetCommandStr( sqlstr );
                }

                runcmd( cmd, null, cmdParms );
                int val = (int)cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                throw new Exception( ex.Message, ex );

            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                {
                    cmd.Connection.Close();

                }
            }
        }
        /// <summary>
        /// 获得存储过程的return值int
        /// </summary>
        /// <param name="procname"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public int Getprocreturn( string procname, SqlParameter[] cmdParms )
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd = GetCommandProc( procname );

                runcmd( cmd, null, cmdParms );

                SqlParameter getreturn = new SqlParameter( "@ret", SqlDbType.Int, 4 ); //定义返回值参数
                getreturn.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add( getreturn );

                cmd.ExecuteNonQuery();

                int getval = Convert.ToInt32( getreturn.Value );
                cmd.Parameters.Clear();

                return getval;
            }
            catch (Exception ex)
            {
                throw new Exception( ex.Message, ex );

            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                {
                    cmd.Connection.Close();

                }
            }
        }

        #endregion

        #region 返回pageproret对象
        /// <summary>
        /// 
        /// </summary>
        /// <param name="procname"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public pageproret GetPageRecordSet( string tbname, int page, int pagesize, string pk, string sort, string fields, string filter )
        {
            SqlDataAdapter adapt = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd = GetCommandProc( "P_DbTable_RowCount_Paging" );
                SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@Tables",SqlDbType.NVarChar,1000),
                new SqlParameter("@PK",SqlDbType.NVarChar,100),
                new SqlParameter("@Sort",SqlDbType.NVarChar,200),
                new SqlParameter("@PageNumber",SqlDbType.Int,4),
                new SqlParameter("@PageSize",SqlDbType.Int,4),
                new SqlParameter("@Fields",SqlDbType.NVarChar,1000),
                new SqlParameter("@Filter",SqlDbType.NVarChar,1000)};
                parms[0].Value = tbname;
                parms[1].Value = pk;
                parms[2].Value = sort;
                parms[3].Value = page;
                parms[4].Value = pagesize;
                parms[5].Value = fields;
                parms[6].Value = filter;
                runcmd( cmd, null, parms );
                adapt = new SqlDataAdapter( cmd );
                adapt.Fill( ds );
                cmd.Parameters.Clear();
                DataTable rettb = ds.Tables[1];
                pageproret ret = new pageproret();
                ret.recordset = rettb;
                ret.pagecount = Convert.ToInt32( ds.Tables[0].DefaultView[0]["PageCount"].ToString() );
                ret.totalcount = Convert.ToInt32( ds.Tables[0].DefaultView[0]["Count"].ToString() );
                return ret;
            }
            catch (Exception ex)
            {
                throw new Exception( ex.Message, ex );
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                {
                    cmd.Connection.Close();

                }
            }
        }

        public pageproret GetPageRecordSet2( int page, int pagesize, string batchId, string strWhere )
        {
            SqlDataAdapter adapt = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd = GetCommandProc( "P_UserDataByBatchIdLog_Select" );
                SqlParameter[] parameters = {					
					new SqlParameter("@BatchId", batchId),                   
                    new SqlParameter("@WhereStr", strWhere),                     
                    new SqlParameter("@pageindex", page),        
                    new SqlParameter("@pagesize", pagesize),     
                                    };
                runcmd( cmd, null, parameters );
                adapt = new SqlDataAdapter( cmd );
                adapt.Fill( ds );
                cmd.Parameters.Clear();
                DataTable rettb = ds.Tables[1];
                pageproret ret = new pageproret();

                ret.recordset = rettb;
                ret.pagecount = Convert.ToInt32( ds.Tables[2].DefaultView[0]["PageTotal"].ToString() );
                ret.totalcount = Convert.ToInt32( ds.Tables[2].DefaultView[0]["RowTotal"].ToString() );
                return ret;
            }
            catch (Exception ex)
            {
                throw new Exception( ex.Message, ex );
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                {
                    cmd.Connection.Close();

                }
            }
        }


        #endregion

        #region runcmd 函数
        /// <summary>
        /// runcmd 函数
        /// </summary>
        /// <param name="myCmd"></param>
        /// <param name="adapt"></param>
        /// <param name="ds"></param>
        private void runcmd( SqlCommand cmd, SqlTransaction trans, SqlParameter[] cmdParms )
        {


            if (cmd.Connection.State != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }
            if (trans != null)
                cmd.Transaction = trans;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add( parm );
            }



        }
        #endregion

        #region datatable转换list方法

        public T CreateItem<T>( DataRow row )
        {
            T obj = default( T );
            if (row != null)
            {
                obj = Activator.CreateInstance<T>();

                foreach (DataColumn column in row.Table.Columns)
                {
                    PropertyInfo prop = obj.GetType().GetProperty( column.ColumnName );
                    if (prop == null)
                    {
                        continue;
                    }

                    try
                    {
                        Type t = prop.PropertyType;

                        object value = row[column.ColumnName];
                        if (!(value == null || value == DBNull.Value))
                        {
                            switch (t.Name)
                            {
                                case "Int64":
                                    value = Convert.ToInt64( value );
                                    break;
                                case "Int32":
                                    value = Convert.ToInt32( value );
                                    break;
                                case "Int16":
                                    value = Convert.ToInt16( value );
                                    break;
                                case "ToBoolean":
                                    value = Convert.ToBoolean( value );
                                    break;
                                case "DateTime":
                                    value = Convert.ToDateTime( value );
                                    break;
                                case "Decimal":
                                    value = Convert.ToDecimal( value );
                                    break;
                                case "Double":
                                    value = Convert.ToDouble( value );
                                    break;
                                case "Byte":
                                    value = Convert.ToByte( value );
                                    break;
                                default:
                                    value = Convert.ToString( value );
                                    break;
                            }
                            prop.SetValue( obj, value, null );
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }

            return obj;
        }
        public IList<T> ConvertTo<T>( IList<DataRow> rows )
        {
            IList<T> list = null;

            if (rows != null)
            {
                list = new List<T>();

                foreach (DataRow row in rows)
                {
                    T item = CreateItem<T>( row );
                    list.Add( item );
                }
            }

            return list;
        }
        public IList<T> ConvertTo<T>( DataTable table )
        {

            if (table == null)
            {
                return null;
            }

            List<DataRow> rows = new List<DataRow>();

            foreach (DataRow row in table.Rows)
            {
                rows.Add( row );
            }

            return ConvertTo<T>( rows );
        }

        #endregion

        #region 分页函数

        public string BuildPager( int page, int page_count, int totalcount, string pager_style, string url_paras )
        {
            return string.Format( "总记录数{0} {1}", totalcount, BuildPager( page, page_count, pager_style, url_paras ) );
        }

        public string BuildPager( int page, int page_count, string pager_style, string url_paras )
        {
            string pageStr = "";
            string scriptname = HttpContext.Current.Request.Url.ToString().Split( '?' )[0];
            if (page > 1)
            {
                pageStr += "<a class='" + pager_style + "' href='?page=1&" + url_paras + "'>首页</a> <a class='" + pager_style + "' href='?page=" + (page - 1) + "&" + url_paras + "'>前一页</a>&nbsp;&nbsp;";
            }

            if (page < page_count)
            {
                pageStr += "<a class='" + pager_style + "' href='?page=" + (page + 1) + "&" + url_paras + "'>后一页</a> <a class='" + pager_style + "' href='?page=" + page_count + "&" + url_paras + "'>末页</a>&nbsp;&nbsp;&nbsp;";
            }

            if (page_count > 3 && page_count <= 80)
            {
                pageStr += "<select name='sltpage' style='font-size:10px;font-family:arial' onchange=window.open('" + scriptname + "?page='+this.value+'&" + url_paras + "','_self')>";
                for (int i = 1; i <= page_count; i++)
                {
                    pageStr += "<option value='" + i + "'";
                    if (i == page)
                        pageStr += " selected";
                    pageStr += ">" + i + "</option>";
                }
                pageStr += "</select>";
                pageStr += "<span style='font:10px arial;color:#333333;'> / " + page_count + "</span>";
            }
            else if (page_count > 80)
            {
                if (page > page_count)
                {
                    HttpContext.Current.Response.Write( "参数错误" );
                    HttpContext.Current.Response.End();
                }
                else
                {
                    pageStr += "<input type='text' id='jump_page_num' style='font:10px arial;width:25px;height:14px;border:solid #aaaaaa 1px;' value='" + page + "'><span style='font:10px arial;color:#333333;'> / " + page_count + "</span>&nbsp;";
                    pageStr += "<input type='button' id='btnJump' value='>>' style='font:9px arial;margin:1px;height:14px;border:solid #aaaaaa 1px;' onclick=window.open('" + scriptname + "?page='+document.getElementById('jump_page_num').value+'&" + url_paras + "','_self')>";
                }
            }
            return pageStr;
        }

        #endregion
    }
}